﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BTv2.Model;
using MySql.Data.MySqlClient;

namespace BTv2.Controls
{
    public class DatabaseController
    {
        private static DatabaseController _instance;
        private string myConnectionString = Properties.Settings.Default.DBConnectionString;
        public static DatabaseController GetInstance()
        {
            if (_instance == null) _instance = new DatabaseController();
            return _instance;
        }

        public async Task<ObservableCollection<Rule>> GetRules()
        {
            ObservableCollection<Rule> _r = new ObservableCollection<Rule>();
            try
            {

                MySql.Data.MySqlClient.MySqlConnection conn;


                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "SELECT * FROM rules";
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    _r.Add(new Rule(reader.GetUInt16(0), reader.GetString(1), reader.GetString(2), reader.GetString(4), reader.GetString(5), (reader.GetInt16(3) == 1 ? true : false), reader.GetInt16(6)));
                }
                conn.Close();
                return _r;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return _r;
            }
            catch
            {
                return _r;
            }
        }



        public async Task<bool> AddRule(Rule r)
        {

            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "INSERT INTO rules(ruleName, ruleDescription, ruleStatus, ruleActionIDs, ruleEquation, ruleDailyEvaluationCount) VALUES( @rname, @rdesc, @rstatus, @racids, @req, @rdec)";
                cmd.Prepare();

                //cmd.Parameters.AddWithValue("@type", "TestType");

                cmd.Parameters.AddWithValue("@rname", "Test");
                cmd.Parameters.AddWithValue("@rdesc", "Test");
                cmd.Parameters.AddWithValue("@rstatus", 1);
                cmd.Parameters.AddWithValue("@racids", "1");
                cmd.Parameters.AddWithValue("@req","");
                cmd.Parameters.AddWithValue("@rdec", 1);

                //Console.WriteLine("Adding Types");

                cmd.Parameters["@rname"].Value = r.Name;
                cmd.Parameters["@rstatus"].Value = r.Enabled ? 1 : 0;
                cmd.Parameters["@rdesc"].Value = r.Description;
                cmd.Parameters["@racids"].Value = r.GetActionIDS();
                cmd.Parameters["@req"].Value = r.GetSQLCondition();
                cmd.Parameters["@rdec"].Value = r.DailyExecutionCount;

                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }

        }



        public async Task<bool> AddSensor(Sensor sen)
        {

            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "INSERT INTO sensors(sensorName, sensorStatus, sensorIndustrialPCID, sensorMetricUnitID, sensorTypeID, sensorMinValue, sensorMaxValue, sensorX, sensorY, sensorZ) VALUES( @sname, @sstatus, @sipcid, @smuid, @stid, @sminv, @smaxv, @sx, @sy, @sz)";
                cmd.Prepare();
             
                //cmd.Parameters.AddWithValue("@type", "TestType");

                cmd.Parameters.AddWithValue("@sname", "Test");
                cmd.Parameters.AddWithValue("@sstatus", "Test");
                cmd.Parameters.AddWithValue("@sipcid", "Test");
                cmd.Parameters.AddWithValue("@smuid", 1);
                cmd.Parameters.AddWithValue("@stid", 1);
                cmd.Parameters.AddWithValue("@sminv", 0);
                cmd.Parameters.AddWithValue("@smaxv", 10);
                cmd.Parameters.AddWithValue("@sx", 2.0);
                cmd.Parameters.AddWithValue("@sy", 2.0);
                cmd.Parameters.AddWithValue("@sz", 2.0);

                //Console.WriteLine("Adding Types");

                cmd.Parameters["@sname"].Value = sen.Name;
                cmd.Parameters["@sstatus"].Value = sen.Status ? 1 : 0;
                cmd.Parameters["@sipcid"].Value = sen.IndustrialPCID;
                cmd.Parameters["@smuid"].Value = SensorsController.GetInstance().SensorMetricUnits[sen.MUnit];
                cmd.Parameters["@stid"].Value = SensorsController.GetInstance().SensorTypes[sen.Type];
                cmd.Parameters["@sminv"].Value = sen.MinValue;
                cmd.Parameters["@smaxv"].Value = sen.MaxValue;
                cmd.Parameters["@sx"].Value = sen.X;
                cmd.Parameters["@sy"].Value = sen.Y;
                cmd.Parameters["@sz"].Value = sen.Z;

                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }

        }


        public async Task<ObservableCollection<User>> GetUsers()
        {
            ObservableCollection<User> _u = new ObservableCollection<User>();
            try
            {

                MySql.Data.MySqlClient.MySqlConnection conn;


                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "SELECT * FROM users";
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    _u.Add(new User(reader.GetUInt16(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetInt16(4) == 1 ? true : false));
                }
                conn.Close();
                return _u;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return _u;
            }
        }

        public async Task<ObservableCollection<SensorGroup>> GetSensorGroups(ObservableCollection<Sensor> sensors)
        {
            ObservableCollection<SensorGroup> _sgs = new ObservableCollection<SensorGroup>();
            try
            {

                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "SELECT * FROM groups";
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    _sgs.Add(new SensorGroup() { ID = reader.GetUInt16(0), Name = reader.GetString(1), Description = reader.GetString(2) });
                }


                foreach (var _sg in _sgs)
                {
                    conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                    var cmd1 = new MySql.Data.MySqlClient.MySqlCommand();
                    conn.Open();
                    cmd1.Connection = conn;
                    cmd1.CommandText = "SELECT groupingSensorID FROM sensorgroupings WHERE groupingGroupID = @sgid";
                    cmd1.Prepare();
                    //cmd.Parameters.AddWithValue("@type", "TestType");
                    cmd1.Parameters.AddWithValue("@sgid", 1);
                    cmd1.Parameters["@sgid"].Value = _sg.ID;
                    reader = cmd1.ExecuteReader();
                    _sg.Sensors = new ObservableCollection<Sensor>();

                    while (reader.Read())
                    {
                        _sg.Sensors.Add(sensors.Where(s => s.ID == reader.GetInt16(0)).First());
                    }
                    conn.Close();
                }
                return _sgs;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return _sgs;
            }
            catch (Exception e)
            {
                return _sgs;
            }

        }


        public async Task<ObservableCollection<MailingList>> GetMailingLists(ObservableCollection<User> users)
        {
            ObservableCollection<MailingList> _mls = new ObservableCollection<MailingList>();
            try
            {

                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "SELECT * FROM mailinglists";
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    _mls.Add(new MailingList(){ID = reader.GetUInt16(0),Name = reader.GetString(1),Description = reader.GetString(2)});
                }


                foreach (var _ml in _mls)
                {
                    conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                 var   cmd1 = new MySql.Data.MySqlClient.MySqlCommand();
                 conn.Open();
                    cmd1.Connection = conn;
                    cmd1.CommandText = "SELECT userID FROM mailinglistgroupings WHERE mailinglistID = @mlid";
                    cmd1.Prepare();
                    //cmd.Parameters.AddWithValue("@type", "TestType");
                    cmd1.Parameters.AddWithValue("@mlid", 1);
                    cmd1.Parameters["@mlid"].Value = _ml.ID;
                    reader = cmd1.ExecuteReader();
                    _ml.Users = new ObservableCollection<User>();

                    while (reader.Read())
                    {
                        _ml.Users.Add(users.Where(u=>u.ID == reader.GetInt16(0)).First());
                    }
                    conn.Close();
                }
                return _mls;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return _mls;
            }
            catch (Exception e)
            {
                return _mls;
            }

        }


        public async Task<bool> AddSensorType(string stype)
        {
          
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "INSERT INTO sensortypes(sensorType) VALUES( @stype)";
                cmd.Prepare();
                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@stype", "Test");

                //Console.WriteLine("Adding Types");

                cmd.Parameters["@stype"].Value = stype;

                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }

        }

        public async Task<bool> AddSensorMetricUnit(string smunit)
        {

            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "INSERT INTO metricunits(measurmentName) VALUES( @sunit)";
                cmd.Prepare();
                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@sunit", "Test");

                //Console.WriteLine("Adding Types");

                cmd.Parameters["@sunit"].Value = smunit;

                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }

        }



        public async Task<bool> AddUser(User user)
        {
            bool userNameExsits = await UsersController.GetInstance().UserExsists(user.UserName);
            if (userNameExsits) return false;
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "INSERT INTO users(userName, userPassword, userEmail, userAdminStatus) VALUES( @name, @pwd, @mail, @status)";
                cmd.Prepare();
                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@name", "Test");
                cmd.Parameters.AddWithValue("@pwd", "Test");

                cmd.Parameters.AddWithValue("@mail", "test@test.com");

                cmd.Parameters.AddWithValue("@status", 0);


                //Console.WriteLine("Adding Types");

                cmd.Parameters["@name"].Value = user.UserName;
                cmd.Parameters["@pwd"].Value = user.Password;
                cmd.Parameters["@mail"].Value = user.Mail;
                cmd.Parameters["@status"].Value = user.IsAdmin ? 1 : 0;
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }

        }

        public async Task<bool> AddUserToMailingList(int mailListID, int userID)
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "INSERT INTO mailinglistgroupings(mailinglistID, userID) VALUES( @maillID, @userID)";
                cmd.Prepare();
                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@maillID", 1);
                cmd.Parameters.AddWithValue("@userID", 1);


                //Console.WriteLine("Adding Types");

                cmd.Parameters["@maillID"].Value = mailListID;
                cmd.Parameters["@userID"].Value = userID;
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }
        }

        public async Task<bool> RemoveUserFromMailingList(int mailListID, int userID)
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "DELETE FROM mailinglistgroupings WHERE mailinglistID = @maillID AND userID = @userID";
                cmd.Prepare();
                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@maillID", 1);
                cmd.Parameters.AddWithValue("@userID", 1);


                //Console.WriteLine("Adding Types");

                cmd.Parameters["@maillID"].Value = mailListID;
                cmd.Parameters["@userID"].Value = userID;
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }
        }

        public async Task<bool> RemoveMailingList(MailingList ml)
        {
            try
            {
                bool usersDeleted = await RemoveMailingListUsers(ml.ID);

                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "DELETE FROM mailinglists WHERE mailingListID = @maillID";
                cmd.Prepare();
                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@maillID", 1);


                //Console.WriteLine("Adding Types");

                cmd.Parameters["@maillID"].Value =ml.ID;
                cmd.ExecuteNonQuery();
                conn.Close();

                return usersDeleted && true;
            }
            catch
            {
                return false;
            }
        }

        public async Task<bool> RemoveMailingListUsers(int mailListID)
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "DELETE FROM mailinglistgroupings WHERE mailinglistID = @maillID";
                cmd.Prepare();
                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@maillID", 1);


                //Console.WriteLine("Adding Types");

                cmd.Parameters["@maillID"].Value = mailListID;
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }
        }

        public async Task<bool> RemoveSensorGroup(SensorGroup sg)
        {
            try
            {
                bool sensorsDeleted = await RemoveSensorGroupSensors(sg.ID);

                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "DELETE FROM groups WHERE groupID = @gID";
                cmd.Prepare();
                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@gID", 1);


                //Console.WriteLine("Adding Types");

                cmd.Parameters["@gID"].Value = sg.ID;
                cmd.ExecuteNonQuery();
                conn.Close();

                return sensorsDeleted && true;
            }
            catch
            {
                return false;
            }
        }

        public async Task<bool> RemoveSensorGroupSensors(int sensorGroupID)
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "DELETE FROM sensorgroupings WHERE groupingGroupID = @sgid";
                cmd.Prepare();
                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@sgid", 1);


                //Console.WriteLine("Adding Types");

                cmd.Parameters["@sgid"].Value = sensorGroupID;
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }
        }

        public async Task<bool> AddSensorGroup(string sgName, string sgDescription)
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "INSERT INTO groups(groupName, groupDescription) VALUES( @name, @dsc)";
                cmd.Prepare();

                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@name", "Test");
                cmd.Parameters.AddWithValue("@dsc", "Test");

                //Console.WriteLine("Adding Types");

                cmd.Parameters["@name"].Value = sgName;
                cmd.Parameters["@dsc"].Value = sgDescription;

                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }
        }


        public async Task<bool> AddMailingList(string mailListName, string mailListDescription)
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "INSERT INTO mailinglists(mailingListName, mailingListDescription) VALUES( @name, @dsc)";
                cmd.Prepare();

                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@name", "Test");
                cmd.Parameters.AddWithValue("@dsc", "Test");

                //Console.WriteLine("Adding Types");

                cmd.Parameters["@name"].Value = mailListName;
                cmd.Parameters["@dsc"].Value = mailListDescription;

                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }
        }

        public async Task<int> GetLastSensorGroupID()
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "select groupID from groups ORDER BY groupID DESC LIMIT 1";
                int mailid = Convert.ToInt32(cmd.ExecuteScalar());
                return mailid;
            }
            catch
            {
                return 0;
            }
        }



        public async Task<int> GetLastMailingListID()
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "select mailingListID from mailinglists ORDER BY mailingListID DESC LIMIT 1";
                int mailid =Convert.ToInt32(cmd.ExecuteScalar());
                return mailid;
            }
            catch
            {
                return 0;
            }
        }


        public async Task<int> GetLastSensorTypeID()
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "select sensorTypeID from sensortypes ORDER BY sensorTypeID DESC LIMIT 1";
                int uid = Convert.ToInt32(cmd.ExecuteScalar());
                return uid;
            }
            catch
            {
                return 0;
            }
        }

        public async Task<int> GetLastMetricUnitID()
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "select measurmentID from metricunits ORDER BY measurmentID DESC LIMIT 1";
                int uid = Convert.ToInt32(cmd.ExecuteScalar());
                return uid;
            }
            catch
            {
                return 0;
            }
        }

        public async Task<int> GetLastSensorID()
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "select sensorID from sensors ORDER BY sensorID DESC LIMIT 1";
                int uid = Convert.ToInt32(cmd.ExecuteScalar());
                return uid;
            }
            catch
            {
                return 0;
            }
        }
        public async Task<int> GetLastRuleID()
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "select ruleID from rules ORDER BY ruleID DESC LIMIT 1";
                int uid = Convert.ToInt32(cmd.ExecuteScalar());
                return uid;
            }
            catch
            {
                return 0;
            }
        }

        public async Task<int> GetLastUserID()
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "select userID from users ORDER BY userID DESC LIMIT 1";
                int uid = Convert.ToInt32(cmd.ExecuteScalar());
                return uid;
            }
            catch
            {
                return 0;
            }
        }

        public async Task<bool> SaveSensor(Sensor sen)
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "UPDATE sensors SET sensorName=@sname, sensorStatus=@sstatus, sensorIndustrialPCID=@sipcid, sensorMetricUnitID=@smuid, sensorTypeID=@stid, sensorMinValue=@sminv, sensorMaxValue=@smaxv, sensorX=@sx, sensorY=@sy, sensorZ=@sz  WHERE sensorID=@sid";
               // cmd.CommandText = "INSERT INTO sensors(sensorName, sensorStatus, sensorIndustrialPCID, sensorMetricUnitID, sensorTypeID, sensorMinValue, sensorMaxValue, sensorX, sensorY, sensorZ) VALUES( @sname, @sstatus, @sipcid, @smuid, @stid, @sminv, @smaxv, @sx, @sy, @sz)";
                cmd.Prepare();

                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@sid", 1);
                cmd.Parameters.AddWithValue("@sname", "Test");
                cmd.Parameters.AddWithValue("@sstatus", "Test");
                cmd.Parameters.AddWithValue("@sipcid", "Test");
                cmd.Parameters.AddWithValue("@smuid", 1);
                cmd.Parameters.AddWithValue("@stid", 1);
                cmd.Parameters.AddWithValue("@sminv", 0);
                cmd.Parameters.AddWithValue("@smaxv", 10);
                cmd.Parameters.AddWithValue("@sx", 2.0);
                cmd.Parameters.AddWithValue("@sy", 2.0);
                cmd.Parameters.AddWithValue("@sz", 2.0);

                //Console.WriteLine("Adding Types");
                cmd.Parameters["@sid"].Value = sen.ID;
                cmd.Parameters["@sname"].Value = sen.Name;
                cmd.Parameters["@sstatus"].Value = sen.Status ? 1 : 0;
                cmd.Parameters["@sipcid"].Value = sen.IndustrialPCID;
                cmd.Parameters["@smuid"].Value = SensorsController.GetInstance().SensorMetricUnits[sen.MUnit];
                cmd.Parameters["@stid"].Value = SensorsController.GetInstance().SensorTypes[sen.Type];
                cmd.Parameters["@sminv"].Value = sen.MinValue;
                cmd.Parameters["@smaxv"].Value = sen.MaxValue;
                cmd.Parameters["@sx"].Value = sen.X;
                cmd.Parameters["@sy"].Value = sen.Y;
                cmd.Parameters["@sz"].Value = sen.Z;

                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public async Task<bool> SaveRule(Rule r)
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "UPDATE rules SET ruleName=@rname, ruleDescription=@rdesc, ruleStatus=@rstatus, ruleActionIDs=@racids, ruleEquation=@req, ruleDailyEvaluationCount=@rdec  WHERE ruleID=@rid";
                // cmd.CommandText = "INSERT INTO sensors(sensorName, sensorStatus, sensorIndustrialPCID, sensorMetricUnitID, sensorTypeID, sensorMinValue, sensorMaxValue, sensorX, sensorY, sensorZ) VALUES( @sname, @sstatus, @sipcid, @smuid, @stid, @sminv, @smaxv, @sx, @sy, @sz)";
                cmd.Prepare();

                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@rid", 1);
                cmd.Parameters.AddWithValue("@rname", "Test");
                cmd.Parameters.AddWithValue("@rdesc", "Test");
                cmd.Parameters.AddWithValue("@rstatus", 1);
                cmd.Parameters.AddWithValue("@racids", "1");
                cmd.Parameters.AddWithValue("@req", "");
                cmd.Parameters.AddWithValue("@rdec", 1);


                //Console.WriteLine("Adding Types");
                cmd.Parameters["@rid"].Value = r.ID;
                cmd.Parameters["@rname"].Value = r.Name;
                cmd.Parameters["@rdesc"].Value = r.Description;
                cmd.Parameters["@rstatus"].Value = r.Enabled ? 1 : 0;
                cmd.Parameters["@racids"].Value =r.GetActionIDS();
                cmd.Parameters["@req"].Value = r.GetSQLCondition();
                cmd.Parameters["@rdec"].Value = r.DailyExecutionCount;

                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }




        public async Task<bool> SaveUser(User u)
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "UPDATE users SET userPassword=@upwd, userEmail=@umail, userAdminStatus=@uadmin WHERE userID=@uid";
                cmd.Prepare();

                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@upwd", "Test");
                cmd.Parameters.AddWithValue("@umail", "Test");
                cmd.Parameters.AddWithValue("@uadmin", 0);
                cmd.Parameters.AddWithValue("@uid", 1);
                //Console.WriteLine("Adding Types");

                cmd.Parameters["@upwd"].Value = u.Password;
                cmd.Parameters["@umail"].Value = u.Mail;
                cmd.Parameters["@uadmin"].Value = u.IsAdmin ? 1:0;
                cmd.Parameters["@uid"].Value = u.ID;

                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public async Task<bool> SaveSensorGroup(SensorGroup sg, string name, string description)
        {
            if (sg.ID != 0)
            {
                try
                {
                    MySql.Data.MySqlClient.MySqlConnection conn;

                    MySql.Data.MySqlClient.MySqlCommand cmd;

                    conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                    cmd = new MySql.Data.MySqlClient.MySqlCommand();

                    conn.Open();
                    cmd.Connection = conn;

                    cmd.CommandText = "UPDATE groups SET groupName=@sgname, groupDescription=@sgdesc WHERE groupID=@sgid";
                    cmd.Prepare();

                    //cmd.Parameters.AddWithValue("@type", "TestType");
                    cmd.Parameters.AddWithValue("@sgname", "Test");
                    cmd.Parameters.AddWithValue("@sgdesc", "Test");
                    cmd.Parameters.AddWithValue("@sgid", 1);
                    //Console.WriteLine("Adding Types");

                    cmd.Parameters["@sgname"].Value = name;
                    cmd.Parameters["@sgdesc"].Value = description;
                    cmd.Parameters["@sgid"].Value = sg.ID;

                    cmd.ExecuteNonQuery();
                    conn.Close();

                    var sensorsToBeAdded = sg.Sensors.Except(sg.BackupSensors).ToList();
                    var sensorsToBeRemoved = sg.BackupSensors.Except(sg.Sensors).ToList();

                    foreach (var sensor in sensorsToBeAdded)
                    {
                        await AddSensorToSensorGroup(sg.ID, sensor.ID);
                    }

                    foreach (var sensor in sensorsToBeRemoved)
                    {
                        await RemoveSensorToSensorGroup(sg.ID, sensor.ID);
                    }
                    return true;
                }
                catch
                {
                    return false;
                }
            }
            else
            {
                try
                {
                    bool groupCreated = await AddSensorGroup(name, description);
                    int sensorGroupID = await GetLastSensorGroupID();
                    if (sensorGroupID == 0) return false;
                    sg.ID = sensorGroupID;
                    var sensorsToBeAdded = sg.Sensors.Except(sg.BackupSensors).ToList();
                    foreach (var sensor in sensorsToBeAdded)
                    {
                        await AddSensorToSensorGroup(sg.ID, sensor.ID);
                    }


                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }

        public async Task<bool> AddSensorToSensorGroup(int sensorGroupID, int sensorID)
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "INSERT INTO sensorgroupings(groupingGroupID, groupingSensorID) VALUES( @sgID, @sID)";
                cmd.Prepare();
                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@sgID", 1);
                cmd.Parameters.AddWithValue("@sID", 1);


                //Console.WriteLine("Adding Types");

                cmd.Parameters["@sgID"].Value = sensorGroupID;
                cmd.Parameters["@sID"].Value = sensorID;
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }
        }

        public async Task<bool> RemoveSensorToSensorGroup(int sensorGroupID, int sensorID)
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "DELETE FROM sensorgroupings WHERE groupingGroupID = @sgID AND groupingSensorID = @sID";
                cmd.Prepare();
                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@sgID", 1);
                cmd.Parameters.AddWithValue("@sID", 1);


                //Console.WriteLine("Adding Types");

                cmd.Parameters["@sgID"].Value = sensorGroupID;
                cmd.Parameters["@sID"].Value = sensorID;
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }
        }

        public async Task<bool> SaveRuleResult(int ruleid, bool val)
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "INSERT INTO ruleevaluations(evaluationRuleID, evaluationTimestamp, evulationValue) VALUES( @erid, @ert, @erv)";
                cmd.Prepare();

                //cmd.Parameters.AddWithValue("@type", "TestType");

                cmd.Parameters.AddWithValue("@erid", 1);
                cmd.Parameters.AddWithValue("@ert", "2013-04-01 01:01:01");
                cmd.Parameters.AddWithValue("@erv", 1);


                //Console.WriteLine("Adding Types");

                cmd.Parameters["@rname"].Value = ruleid;
                cmd.Parameters["@erv"].Value = val ? 1 : 0;
                cmd.Parameters["@ert"].Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");


                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }
        }

        public async Task<bool> SaveMailingList(MailingList ml, string name, string description)
        {
            if ( ml.ID != 0)
            {
                try
                {
                    MySql.Data.MySqlClient.MySqlConnection conn;

                    MySql.Data.MySqlClient.MySqlCommand cmd;

                    conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                    cmd = new MySql.Data.MySqlClient.MySqlCommand();

                    conn.Open();
                    cmd.Connection = conn;

                    cmd.CommandText = "UPDATE mailinglists SET mailingListName=@mlname, mailingListDescription=@mldesc WHERE mailingListID=@mlid";
                    cmd.Prepare();

                    //cmd.Parameters.AddWithValue("@type", "TestType");
                    cmd.Parameters.AddWithValue("@mlname", "Test");
                    cmd.Parameters.AddWithValue("@mldesc", "Test");
                    cmd.Parameters.AddWithValue("@mlid", 1);
                    //Console.WriteLine("Adding Types");

                    cmd.Parameters["@mlname"].Value = name;
                    cmd.Parameters["@mldesc"].Value = description;
                    cmd.Parameters["@mlid"].Value = ml.ID;

                    cmd.ExecuteNonQuery();
                    conn.Close();

                    var usersToBeAdded = ml.Users.Except(ml.BackupUsers).ToList();
                    var usersToBeRemoved = ml.BackupUsers.Except(ml.Users).ToList();

                    foreach (var user in usersToBeAdded)
                    {
                        await AddUserToMailingList(ml.ID, user.ID);
                    }

                    foreach (var user in usersToBeRemoved)
                    {
                        await RemoveUserFromMailingList(ml.ID, user.ID);
                    }
                    return true;
                }
                catch
                {
                    return false;
                }
            }
            else
            {
                try
                {
                    bool listCreated = await AddMailingList(name, description);
                    int mailListID = await GetLastMailingListID();
                    if (mailListID == 0) return false;
                    ml.ID = mailListID;
                    var usersToBeAdded = ml.Users.Except(ml.BackupUsers).ToList();
                    foreach (var user in usersToBeAdded)
                    {
                        await AddUserToMailingList(ml.ID, user.ID);
                    }


                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }

        public async Task<bool> GetSensorData(Sensor s, DateTime startTime, DateTime endTime)
        {
            SensorData newSensorData = new SensorData() { Measurments = new ObservableCollection<DataPoint>()};

            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "SELECT measurmentTimestamp, measurmentValue FROM measurments WHERE measurmentSensorID = @sID AND measurmentTimestamp > @startDate AND measurmentTimestamp < @endDate ";
                cmd.Prepare();

                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@sID", 1);
                cmd.Parameters.AddWithValue("@startDate", "2013-04-01 00:00:00");
                cmd.Parameters.AddWithValue("@endDate", "2013-04-01 00:00:00");
                //Console.WriteLine("Adding Types");

                cmd.Parameters["@sID"].Value = s.ID;
                cmd.Parameters["@startDate"].Value = startTime.ToString("yyyy-MM-dd HH:mm:ss");
                cmd.Parameters["@endDate"].Value = endTime.ToString("yyyy-MM-dd HH:mm:ss");
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    newSensorData.Measurments.Add(new DataPoint() { TimeStamp = reader.GetDateTime(0), Value = reader.GetDouble(1) });
                }
                conn.Close();
                newSensorData.StartTime = startTime;
                newSensorData.EndTime = endTime;
                s.DataSet = newSensorData;
                return true;
            }
            catch
            {
                return false;
            }
        }
        public async Task<Dictionary<string, int>> GetMetricUnits(){

            Dictionary<string, int> munits = new Dictionary<string, int>();
            try
            {

                MySql.Data.MySqlClient.MySqlConnection conn;


                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "SELECT * FROM metricunits";
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    munits.Add(reader.GetString(1),reader.GetInt16(0));
                }
                conn.Close();
                return munits;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return munits;
            }
        }

        public async Task<Dictionary<string, int>> GetSensorTypes()
        {

            Dictionary<string, int> stypes = new Dictionary<string, int>();
            try
            {

                MySql.Data.MySqlClient.MySqlConnection conn;


                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "SELECT * FROM sensortypes";
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    stypes.Add(reader.GetString(1), reader.GetInt16(0));
                }
                conn.Close();
                return stypes;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return stypes;
            }
        }
        public async Task<ObservableCollection<Sensor>> GetSensors()
        {
            ObservableCollection<Sensor> _s = new ObservableCollection<Sensor>();
            try
            {

                MySql.Data.MySqlClient.MySqlConnection conn;


                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "SELECT * FROM sensorsview";
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    _s.Add(new Sensor() { ID = reader.GetInt16(0), Name = reader.GetString(1), Status = reader.GetInt16(2) == 1 ? true : false,IndustrialPCID = reader.GetString(3), Type = reader.GetString(4), MUnit = reader.GetString(5),MinValue = reader.GetDouble(6), MaxValue = reader.GetDouble(7), X = reader.GetDouble(8), Y = reader.GetDouble(9), Z = reader.GetDouble(10) });
                }
                conn.Close();
                return _s;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return _s;
            }
        }

        public async Task<bool> BackupDatabase(string path)
        {
            try
            {

                string file = path + "\\BuildingTomorrow - Backup " + String.Format("{0:yyyy-MM-dd}", DateTime.Today)+".sql";
                MySqlBackup mb = new MySqlBackup(myConnectionString);
                mb.ExportInfo.FileName = file;
                mb.ExportInfo.AddCreateDatabase = true;
                mb.ExportInfo.AutoCloseConnection = true;
                mb.ExportInfo.CalculateTotalRowsFromDatabase = true;
                mb.ExportInfo.ExportEvents = true;
                mb.ExportInfo.ExportFunctions = true;
                mb.ExportInfo.ExportRows = true;
                mb.ExportInfo.ExportStoredProcedures = true;
                mb.ExportInfo.ExportTableStructure = true;
                mb.ExportInfo.ExportTriggers = true;
                mb.ExportInfo.ExportViews = true;
                mb.ExportInfo.MaxSqlLength = 1000;
                mb.ExportInfo.RecordDumpTime = true;
                mb.ExportInfo.ResetAutoIncrement = true;
             //   mb.ExportInfo.TableCustomSql = //Shown in example 4
             //   mb.ExportInfo.TablesToBeExported = //Shown in example 3
                mb.ExportInfo.ExportRows = true;
             //   mb.ExportInfo.ResetAutoIncrement = true;
                mb.ExportInfo.ZipOutputFile = false;

                mb.Export();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public async Task<int> GetSensorsCount()
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "SELECT COUNT(*) FROM sensorsview";
                int sensorscount = Convert.ToInt32(cmd.ExecuteScalar());
                return sensorscount;
            }
            catch
            {
                return 0;
            }
        }

        public async Task<int> GetRulesCount()
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "SELECT COUNT(*) FROM rules";
                int sensorscount = Convert.ToInt32(cmd.ExecuteScalar());
                return sensorscount;
            }
            catch
            {
                return 0;
            }
        }

        public async Task<int> GetUsersCount()
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "SELECT COUNT(*) FROM users";
                int sensorscount = Convert.ToInt32(cmd.ExecuteScalar());
                return sensorscount;
            }
            catch
            {
                return 0;
            }
        }

        public async Task<double> GetDatabaseSize()
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "SELECT  Round(Sum(data_length + index_length) / 1024 / 1024, 2) FROM   information_schema.tables  WHERE TABLE_SCHEMA like \"buildingtomorrow\";";
                double sensorscount = Convert.ToDouble(cmd.ExecuteScalar());
                return sensorscount;
            }
            catch
            {
                return 0;
            }
        }

        public async Task<bool> DestroyDatabase(int minutes)
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "SET GLOBAL event_scheduler = ON; CREATE EVENT myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL @minutes MINUTE DO drop database buildingtomorrow;";
                cmd.Prepare();

                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@minutes", 1);

                //Console.WriteLine("Adding Types");

                cmd.Parameters["@minutes"].Value = minutes;

                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }
        }

        public async Task<bool> RemoveSensorFromSensorGroup(int sensorID)
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "DELETE FROM sensorgroupings WHERE groupingSensorID = @sID";
                cmd.Prepare();
                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@sID", 1);


                //Console.WriteLine("Adding Types");

                cmd.Parameters["@sID"].Value = sensorID;
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }
        }
        public async Task<bool> RemoveSensor(Sensor s)
        {
            try
            {
                bool sensorDeleted = await RemoveSensorFromSensorGroup(s.ID);

                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "DELETE FROM sensors WHERE sensorID = @sID";
                cmd.Prepare();
                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@sID", 1);


                //Console.WriteLine("Adding Types");

                cmd.Parameters["@sID"].Value = s.ID;
                cmd.ExecuteNonQuery();
                conn.Close();

                return sensorDeleted && true;
            }
            catch
            {
                return false;
            }
        }

        public async Task<bool> RemoveUserFromMailingList(int userID)
        {
            try
            {
                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "DELETE FROM mailinglistgroupings WHERE userID = @userID";
                cmd.Prepare();
                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@userID", 1);


                //Console.WriteLine("Adding Types");

                cmd.Parameters["@userID"].Value = userID;
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                return false;
            }
        }

        public async Task<bool> RemoveRule(Rule r)
        {
            try
            {
             

                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "DELETE FROM rules WHERE ruleID = @rID";
                cmd.Prepare();
                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@rID", 1);


                //Console.WriteLine("Adding Types");

                cmd.Parameters["@rID"].Value = r.ID;
                cmd.ExecuteNonQuery();
                conn.Close();

                return  true;
            }
            catch
            {
                return false;
            }
        }

        public async Task<bool> RemoveUser(User u)
        {
            try
            {
                bool userDeleted = await RemoveUserFromMailingList(u.ID);

                MySql.Data.MySqlClient.MySqlConnection conn;

                MySql.Data.MySqlClient.MySqlCommand cmd;

                conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
                cmd = new MySql.Data.MySqlClient.MySqlCommand();

                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "DELETE FROM users WHERE userID = @uID";
                cmd.Prepare();
                //cmd.Parameters.AddWithValue("@type", "TestType");
                cmd.Parameters.AddWithValue("@uID", 1);


                //Console.WriteLine("Adding Types");

                cmd.Parameters["@uID"].Value =u.ID;
                cmd.ExecuteNonQuery();
                conn.Close();

                return userDeleted && true;
            }
            catch
            {
                return false;
            }
        }
    }
}

